package cn.harry.cabinet.database

import android.content.Context
import android.util.Log
import androidx.room.Database
import androidx.room.Room
import androidx.room.RoomDatabase
import androidx.room.TypeConverters
import androidx.sqlite.db.SupportSQLiteDatabase
import cn.harry.cabinet.dao.CabinetDao
import cn.harry.cabinet.dao.CardDao
import cn.harry.cabinet.dao.ConfigurationDao
import cn.harry.cabinet.dao.HardwareDeviceDao
import cn.harry.cabinet.dao.UsageRecordDao
import cn.harry.cabinet.dao.UserCabinetDao
import cn.harry.cabinet.dao.UserDao
import cn.harry.cabinet.database.dao.OperationLogDao
import cn.harry.cabinet.model.entity.Cabinet
import cn.harry.cabinet.model.entity.Card
import cn.harry.cabinet.model.entity.Configuration
import cn.harry.cabinet.model.entity.HardwareDevice
import cn.harry.cabinet.model.entity.OperationLog
import cn.harry.cabinet.model.entity.UsageRecord
import cn.harry.cabinet.model.entity.User
import cn.harry.cabinet.model.entity.UserCabinet
import cn.harry.cabinet.utils.DateConverter

/**
 * 应用数据库
 * Room数据库的入口点
 */
@Database(
    entities = [
        Cabinet::class,
        User::class,
        UserCabinet::class,
        Configuration::class,
        UsageRecord::class,
        OperationLog::class,
        HardwareDevice::class,
        Card::class
    ],
    version = 1,  // 重置为版本1（需要清除应用数据）
    exportSchema = false
)
@TypeConverters(DateConverter::class)
abstract class AppDatabase : RoomDatabase() {

    abstract fun cabinetDao(): CabinetDao
    abstract fun userDao(): UserDao
    abstract fun userCabinetDao(): UserCabinetDao
    abstract fun configurationDao(): ConfigurationDao
    abstract fun usageRecordDao(): UsageRecordDao
    abstract fun operationLogDao(): OperationLogDao
    abstract fun hardwareDeviceDao(): HardwareDeviceDao
    abstract fun cardDao(): CardDao

    companion object {
        private const val TAG = "AppDatabase"

        @Volatile
        private var INSTANCE: AppDatabase? = null


        fun getDatabase(context: Context): AppDatabase {
            return INSTANCE ?: synchronized(this) {
                val instance = Room.databaseBuilder(
                    context.applicationContext,
                    AppDatabase::class.java,
                    "app_database"
                )
                    .fallbackToDestructiveMigration() // ⚠️ 这会清空数据库！
//                .addMigrations(MIGRATION_3_4)  // ✅ 添加迁移策略，保留用户数据
                    .addCallback(DatabaseCallback(context.applicationContext))  // 添加数据库回调
                    .build()

                INSTANCE = instance
                instance
            }
        }

        /**
         * 清除数据库实例（用于测试）
         */
        fun clearInstance() {
            INSTANCE = null
        }
    }

    /**
     * 数据库回调 - 在数据库创建时执行初始化
     */
    private class DatabaseCallback(
        private val context: Context
    ) : Callback() {

        override fun onCreate(db: SupportSQLiteDatabase) {
            super.onCreate(db)
            Log.i(TAG, "数据库首次创建，开始执行初始化...")

            // 执行 SQL 初始化文件
            executeSqlFile(db, "database/init_data.sql")
        }

        /**
         * 执行 assets 中的 SQL 文件
         */
        private fun executeSqlFile(db: SupportSQLiteDatabase, fileName: String) {
            try {
                Log.i(TAG, "开始读取 SQL 文件: $fileName")
                val inputStream = context.assets.open(fileName)
                val sqlStatements = inputStream.bufferedReader().use { it.readText() }
                
                Log.i(TAG, "SQL 文件内容长度: ${sqlStatements.length} 字符")

                // 先按行过滤注释和空行，再按分号分割
                val cleanedSql = sqlStatements.lines()
                    .filter { line ->
                        val trimmed = line.trim()
                        trimmed.isNotEmpty() && !trimmed.startsWith("--")
                    }
                    .joinToString("\n")
                
                // 按分号分割 SQL 语句
                val sqlList = cleanedSql.split(";")
                    .map { it.trim() }
                    .filter { it.isNotEmpty() }
                
                Log.i(TAG, "共解析出 ${sqlList.size} 条 SQL 语句")
                
                var successCount = 0
                var failCount = 0
                
                sqlList.forEachIndexed { index, sql ->
                    try {
                        db.execSQL(sql)
                        successCount++
                        Log.d(TAG, "✅ SQL ${index + 1}/${sqlList.size} 执行成功: ${sql.take(50)}...")
                    } catch (e: Exception) {
                        failCount++
                        Log.e(TAG, "❌ SQL ${index + 1}/${sqlList.size} 执行失败: ${e.message}")
                        Log.e(TAG, "失败的 SQL: ${sql.take(200)}...", e)
                    }
                }

                Log.i(TAG, "✅ 数据库初始化完成 - 成功: $successCount, 失败: $failCount")
            } catch (e: Exception) {
                Log.e(TAG, "❌ 读取 SQL 文件失败: $fileName", e)
            }
        }
    }
}